Lookup Data

Bordereaux  | Documents | User Experience

The Lookup Table feature allows you to define and maintain data look-up and validation tables used by data mapping and validation rules.

In Bordereaux, look-up tables are used by the Rules Engine to present data as a list for derived data elements as defined within the Mapping Template Manager. You can create as many tables as needed to support those field derivations. Base look-up table definitions and their criteria are created manually or by importing Excel or CSV files. Once you create a look-up table, you can rename, clone, or delete it. You can also export look-up file data. This capability is useful when you have numerous, multiple data records to change, update or reorganize within a table. 

The Lookup Data feature is accessible within the Data Management (Bordereau), Document, and User Experience services. Note the link for accessing Lookup Data differs depending on your implementation.

 

  1. From the Navigation panel (if in Bordereaux), click Lookup Data.

  2. Click New Lookup. 

  3. Choose a new name by overwriting the placemark text, NewLookupName. Click OK. 

    Click the image to change its size.

  4. Complete the following steps to configure your lookup:
    Click the image to change its size.

    • Lookup Type 
      Use the radio button to select whether your lookup returns:

      • Standard (default) - a one-to-one result and view only one record at a time.

      • Range - multiple rows to view. 

    • # of Standard Search Items 
      Use this drop-down field to select the number of columns (search items) the system will access before returning a value.  When you select 1 from the drop-down, you’ll be presented with a Label #1 field. When you increase the number, the system adds additional rows for you to complete. 
      Click the image to change its size.

      For our example, we will use 1 as the # of Standard Search Items and give it a label of BrokerCode. The drop-down to the right of the Label allows you to define the format of the data being searched. Is it a currency? Is it text?  You can select the appropriate format in the drop-down list. A completed example is shown below:

    • You can leave the default label name as they are, Label1, Label2, and so on or you can provide more meaningful names for searches. For example, when searching for Brokers, Label1 might be BrokerID.

    • # of Return Values 
      This represents the number of fields of information that can be returned based on the Search Items.  Continuing with the Broker example, in the image shown below, the first column represents the 1 Standard Search Item (orange highlight). The columns in light green represent all the potential values under Return Values.  In this case, there are 10; therefore, we would select 10 from the drop-down list provided.  (10 is the maximum number of values the system returns). 

      When you select a number of return values from the drop-down list, the same number of fields displays with default values of ReturnLabel1, ReturnLabel2, etc.  You can leave the defaults, but it is recommended to label them something meaningful so that when they are used later in Workflows, the data is easily identifiable.  For example, using the data below, the first Return Label might be called Brokerage.  The second, BrokerageAddress.  The third, BrokerContact.  This way when they are used to populate data, the user can easily identify what data they are using.  A completed example is shown below:





    • Effective Date
      Represents a starting date when the look-up table you are defining is effective. The date automatically defaults to the date the look-up table is defined but you can change it to a future date or edit it later to a different date.

    • Expiration Date
      This is the date through which a Lookup is valid.  For example, if one list applies for 2021, but an entirely different list applies for 2022, you might put an expiration date of 12/31/2021 for one list, while putting an expiration date for the 2022 list as 12//31/2022.  By default, this field is blank which means the Lookup does not expire and will always be available for use unless otherwise specified.

    • Transaction Type 
      The radio button allows the user to define whether the Lookup will apply to specify types of activity.  By default, it is assumed that the Look-up function will apply whether a transaction is a new one or a renewal; however, if it is appropriate to limit lookups to either new or renewal transactions, simply select the appropriate radio button.

    • Subsequent Lookup
      Use the drop-down list provided to select a follow-up or secondary Lookup table that should process subsequent to/following the lookup currently being defined

    • Description 
      Free-form text field that allows you to give a description to the Lookup (that is, used to look up broker contact and commission information)

  1. Click Save to save the look-up table you created. Once saved, a green message block appears in the upper left corner of your screen and confirms the save was successful.  (You may need to scroll back up to see the top of the page.)

Import Excel or CSV:  Once you have created and saved the look-up table, you can use this feature to load information into the table.

  • Click on the Import Excel or CSV button (lower left-hand corner of screen). Once clicked, a pop-up window, Import Data, appears. 

  • Click Choose File to display the File Explorer pop-up window .  Double-click the file you intend to import. (Note: You can also single-click the file and then click Open.)

  • You should now see the file you selected in the Choose File field.  Click on Import File.

  • The look-up table should now be populated with data (you might need to scroll to the right to see all the data, as in the second image below):

Once the data is imported, it can now be modified as needed.  There are five Actions at the end of each data row, they are as follows:

  • Delete 
    As we can see in the first row of the data above, this is really a Header that was in the Excel file we imported.  Use the Delete key, to remove the row.  A confirmation box will pop up.  Click OK to confirm and delete the row or click Cancel to and return to the table data screen, without saving changes.

  • Edit 
    If you want to change values in a single row of data, you can click the Edit button.  An Edit Row window will open and you can then manually update the information in the row.  Click OK to save the information and return to the Lookup Data table data or Cancel to exit out of the Edit Row window.

  • The Up/Down Arrows allow you to move a record either up or down the list. Note:  Since this is a lookup and not a list you will likely not need to change the order of the records shown.

  • The Clone button replicates a selected row. The system duplicated the row then you can manually edit the information in the newly cloned row.

You can also, from the main Lookups screen, use the drop-down field just after the Lookup Name field, to switch from Details to Data. The Data version of the screen allows the user to perform other tasks such as:

  • Add Row 
    Found in the lower left corner of the page, this option allows you to manually add a new, blank row to the table.

  • Rename 
    This allows the user to re-name the Lookup. When selected, the Rename Lookup dialog appears. Type the new designed name in the New Name field then click OK to save the name change or Cancel to exit without saving.

  • Export File 
    The Export File feature allows you to export the entire contents of a look-up table. It is useful when you have long lists of information to update in a table and prefer to avoid using the Lookup Manager settings. While you can copy and paste numerous rows at one time in Excel, the tool only lets you add one at a time. With the Export File feature, you can export the look-up rows, add more in Excel, then import the modified rows again with the additional data. When you use the Export File feature, the exported file will appear as a downloaded file on your screen.